﻿ 
// 创建人  ：LinJian
// 创建时间：2007年5月23日
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using WF_DataAccess;
using System.Configuration;
using WF_Business;
using Business.Admin;
using System.Data.OracleClient;
using Common;

namespace Business.FlowOperation

{
    /// <!--
    /// 功能描述  : 根据用户获取工作流信息（类似WorkFlowHandle）
    /// 创建人  : LinJian
    /// 创建时间: 2007年5月23日
    /// -->
    public class ClsUserWorkFlow
    {
        /// <summary>
        /// 根据用户id获取所能受理的工作流
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <returns></returns>
        public DataTable GetWorkFlowByUserId(string strUserId)
        {
            string strSql = string.Format(@"select m.* from st_workflow m, st_step n where m.WID in (select WID from st_group_in_step where GID in (select GID from st_user_group t
                                                where t.userid = '{0}') And step_type=0 ) and m.WID = n.Wid and n.step_type = 0 and m.rot = 0 order by m.wname", strUserId);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 判断此工作流是否存在
        /// </summary>
        /// <param name="strFlowName">工作流名称</param>
        /// <param name="strFlowType">工作流类型</param>
        /// <returns></returns>
        public string IsHaveWorkFlow(string strFlowName,string strFlowType)
        {
            string strSql = string.Format("select FLOWNAME from xt_workflow_define where flowname='{0}'"+
                " and flowtype='{1}'", strFlowName, strFlowType);
            string sWorkFlow = SysParams.OAConnection().GetValue(strSql);
            if (string.IsNullOrEmpty(sWorkFlow))
            {
                sWorkFlow = "new";       
            }
            return sWorkFlow;
        }

        
        /// <summary>
        /// 根据id更新指定业务的优先级别
        /// </summary>
        /// <param name="strSerialId">业务id</param>
        /// <param name="strSerialStatus">业务休闲级</param>
        /// <returns></returns>
        public int UpdateInstancePriority(string strSerialId, string strSerialStatus)
        {
            
            string strSql = string.Format("update st_instance set priority='{0}' where iid='{1}'", strSerialStatus, strSerialId);
            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取工作流的名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetFlowName()
        {
            
            string strSql = "select distinct wname from st_workflow where wid in(select wid from st_instance where 1=1)";
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);

            return dtOut;
        }

        /// <summary>
        /// 判断是否回退过
        /// </summary>
        /// <param name="strInstanceId"></param>
        /// <returns></returns>
        public string GetIsBack(string strInstanceId)
        {
            string strSql = string.Format(@"SELECT SNAME FROM ST_FLOW WHERE Wid in(SELECT Wid FROM ST_INSTANCE WHERE IID='{0}') AND Is_Back=1", strInstanceId);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 根据iid获取工作流名称
        /// </summary>
        /// <param name="strWid"></param>
        /// <returns></returns>
        public string GetFlowNameByIid(string strIid)
        {
            
            string strSql = string.Format(@"select distinct wname from st_workflow a,st_instance b where a.wid=b.wid ",
                strIid);

            if(!string.IsNullOrEmpty(strIid))
            {
                 strSql += string.Format(@" and iid='{0}'",strIid);
            }

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取新流程
        /// </summary>
        /// <returns></returns>
        public static DataTable GetNewWorkflowNoFree()
        {
            DataTable table;
            string sql = "select wid,wname from st_workflow where rot=0";
            SysParams.OAConnection().RunSql(sql, out table);
            return table;
        }

        /// <summary>
        /// 根据id获取工作流名称
        /// </summary>
        /// <param name="strWid"></param>
        /// <returns></returns>
        public string GetFlowNameByWid(string strWid)
        {
            string strSql = string.Format("select wname from st_workflow where wid='{0}'",
                strWid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 根据流程名称获取最高版本工作流id
        /// </summary>
        /// <param name="strName">工作流名称</param>
        /// <returns></returns>
        public string GetFlowIdByName(string strName)
        {
            string strSql = string.Format("select wid from st_workflow where wname='{0}' and rot=0", strName);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 更新流程督办时间
        /// </summary>
        /// <param name="strSerialId">业务ID</param>
        /// <param name="strValue">更新的值</param>
        /// <!--
        /// 创建人  : cd
        /// 创建时间: 2007-07-10
        /// -->
        public int UpdateTime(string strSerialId, string strTimeValue)
        {
            
            string strSql = string.Format("update st_instance set accepted_time=to_date('{0}','yyyy-mm-dd hh24:mi:ss'), " +
                "exbeginmonitor=to_date('{1}','yyyy-mm-dd hh24:mi:ss')" +
                " where iid='{2}' ", strTimeValue, strTimeValue, strSerialId);
            return SysParams.OAConnection().RunSql(strSql);
        }


        #region 获取登录用户有权限查看的所有流程名
        /// <summary>
        /// 获取登录用户有权限查看的所有流程名
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <returns></returns>
        public DataTable GetFlowNameByUserID(string userid)
        {
            //判断当前登录用户是否为系统管理员
            bool ISsystemUser = StUserGroupHandle.IsSystemUser(userid);
            string strSql = string.Empty;
            if (!ISsystemUser)
            {
                
                strSql = string.Format(@"select distinct wname from st_workflow p
                    where exists (select distinct wid from st_instance a where a.wid=p.wid and a.isdelete <>1)
                    and exists (select task from xt_query_right t where t.userid = '{0}' and t.task=p.wname) 
                    order by length(wname) ", userid);
            }
            else
            {
                strSql = @"select distinct wname from st_workflow p
                 where exists (select distinct wid from st_instance a where a.wid=p.wid )
                 order by length(wname)";
            }

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        } 
        #endregion

        #region 获取登录用户有权限查看的所有流程名,效率监察部分
        /// <summary>
        /// 获取登录用户有权限查看的所有流程名,效率监察部分
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="isLVJC"></param>
        /// <returns></returns>
        public DataTable GetFlowNameByUserID(string userid, bool isLVJC)
        {
            //判断当前登录用户是否为系统管理员
            bool ISsystemUser = StUserGroupHandle.IsSystemUser(userid);
            string strSql = string.Empty;
            DataTable dtTemp = new DataTable();
            DataColumn col = new DataColumn("wname");
            dtTemp.Columns.Add(col);
            if (!ISsystemUser)
            {
                
                DataTable dtOut;
                strSql = string.Format(@"select distinct wname from st_workflow p
                    where exists (select distinct wid from st_instance a where a.wid=p.wid )
                    and exists (select task from xt_query_right t where t.userid = '{0}' and t.task=p.wname) 
                    order by length(wname) ", userid);
                SysParams.OAConnection().RunSql(strSql, out dtOut);
                foreach (DataRow row in dtOut.Rows)
                {
                    DataRow rownew = dtTemp.NewRow();
                    rownew["wname"] = row[0].ToString();
                    dtTemp.Rows.Add(rownew);
                }
            }
            else
            {
                //先显示处罚的流程
                
                strSql = string.Format(@"select distinct wname
                     from st_workflow p
                     where exists (select distinct wid from st_instance a where a.wid = p.wid  )
                     and exists(select flowname from xt_workflow_define where flowtype='处罚' and flowname=p.wname)");
                DataTable dtOut;
                SysParams.OAConnection().RunSql(strSql, out dtOut);
                foreach (DataRow row in dtOut.Rows)
                {
                    DataRow rownew = dtTemp.NewRow();
                    rownew["wname"] = row[0].ToString();
                    dtTemp.Rows.Add(rownew);
                }
                //后显示其他流程，按照flowtype和flownum排序
                
                strSql = string.Format(@" select wname from
                     (
                     select distinct p.wname,w.flowtype,w.flownum
                      from st_workflow p,xt_workflow_define w
                     where exists (select distinct wid from st_instance a where a.wid = p.wid )
                     and p.wname=w.flowname and w.flowtype<>'处罚' 
                     )order by flowtype,flownum");
                SysParams.OAConnection().RunSql(strSql, out dtOut);
                foreach (DataRow row in dtOut.Rows)
                {
                    DataRow rownew = dtTemp.NewRow();
                    rownew["wname"] = row[0].ToString();
                    dtTemp.Rows.Add(rownew);
                }
                //然后显示流程不在xt_workflow_define表里的
                strSql = string.Format(@"select distinct wname from st_workflow p
                    where exists (select distinct wid from st_instance a where a.wid=p.wid )
                    and not exists(select flowname from xt_workflow_define where flowname=p.wname)");
                SysParams.OAConnection().RunSql(strSql, out dtOut);
                foreach (DataRow row in dtOut.Rows)
                {
                    DataRow rownew = dtTemp.NewRow();
                    rownew["wname"] = row[0].ToString();
                    dtTemp.Rows.Add(rownew);
                }
            }

            return dtTemp;
        } 
        #endregion


        #region 获得流程超期的方法
        /// <summary>
        /// 获得流程超期的方法
        /// </summary>
        /// <param name="strWorkFlowName">工作流程id</param>
        /// <param name="strDateStart">开始日期</param>
        /// <param name="strDateEnd">终止日期</param>
        /// <param name="strOverFlag">流程办结的标志(1表示办结)</param>
        /// <param name="userid">当前登录用户ID</param>
        /// <returns></returns>
        /// 修改事由 ：岗位、业务、挂起 可以同时查询超期案件
        public DataTable GetAllInstanceStatusInfo(String strWorkFlowName, String strDateStart, String strDateEnd,
            String strOverFlag, string userid)
        {
            
            string sql = @"select a.wid, a.iid, a.name, a.accepted_time,a.ExTotalTime,a.Exusedtime,
                (select wname from st_workflow p where p.wid=a.wid) wname,
(select step from st_work_item where wiid in (select max(wiid) from st_work_item where iid=a.iid)) curStepName,
(select n.user_name from st_work_item m,st_user n where m.userid=n.userid and m.wiid in (select max(wiid) from st_work_item where iid=a.iid)) curUserName 
                from st_instance a where 1=1 ";

            //判断当前登录用户是否为系统管理员
            bool ISsystemUser = StUserGroupHandle.IsSystemUser(userid);

            if (!ISsystemUser)//if not SystemManUser
            {
                sql += string.Format(@" and a.wid in (select s.wid  from st_workflow s, 
                    xt_query_right t where s.wname = t.task and t.userid = '{0}')", userid);
            }

            if (!string.IsNullOrEmpty(strWorkFlowName) && strWorkFlowName != "1")//不是查找所有流程的话
            {
                sql += string.Format(" and a.wid in (select wid from st_workflow where wname='{0}')", strWorkFlowName);
            }

            if (!string.IsNullOrEmpty(strDateStart) && !string.IsNullOrEmpty(strDateEnd))
            {
                sql +=string.Format(@" and a.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS') 
                    and a.accepted_time<=to_date('{1}','YYYY-MM-DD HH24:MI:SS')",strDateStart,strDateEnd);
            }

            if (strOverFlag.Equals("1"))//查找办结的业务
            {
                sql += " and (a.status=2 or a.status=-2)";
            }
            else if (strOverFlag.Equals("2"))//未办结
            {
                sql += " and a.status!=2 and a.status!=-2";
            }
            else if (strOverFlag == "21")//业务延期
            {
                sql += " and exusedtime>extotaltime";
            }
            else if (strOverFlag == "31")//岗位延期
            {
                sql += @" and exists (select distinct iid from st_work_item where exusedtime>extotaltime and iid=a.iid)
                           and exists (select distinct iid from st_work_item_hist where exusedtime>extotaltime and iid=a.iid)";
                //sql += " and exists (select t.iid from xt_inspect t where t.事件类型='1' and t.iid=a.iid) ";
            }
            else if (strOverFlag == "41")//挂起
            {
                sql += " and exists (select iid from st_suspend t where t.iid=a.iid)";
            }

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            return dtOut;
        } 
        #endregion
        
        #region 更改步骤督办时间
        /// <summary>
        /// 更改步骤督办时间
        /// </summary>
        /// <param name="StepID">步骤ID</param>
        /// <param name="strAccTime">接收时间</param>
        /// <param name="strSubTime">提交时间</param>
        /// <!--
        /// 创建人  : cd
        /// 创建时间: 2007-07-10
        /// -->
        public void UpdateStepTime(string StepID, string strAccTime, string strSubTime)
        {
            //更新历史步骤表
            string strSql = "update st_work_item_hist set accepted_time=to_date('{0}','yyyy-mm-dd hh24:mi:ss'), ";
            strSql = strSql + "exbeginmonitor=to_date('{1}','yyyy-mm-dd hh24:mi:ss'),submit_time=to_date('{2}','yyyy-mm-dd hh24:mi:ss') where WIID='{3}'";
            strSql = string.Format(strSql, strAccTime, strAccTime, strSubTime, StepID);
            SysParams.OAConnection().RunSql(strSql);

            //更新当前步骤表
            strSql = "update st_work_item set accepted_time=to_date('{0}','yyyy-mm-dd hh24:mi:ss'), ";
            strSql = strSql + "exbeginmonitor=to_date('{1}','yyyy-mm-dd hh24:mi:ss') where WIID='{2}'";
            strSql = string.Format(strSql, strAccTime, strAccTime, StepID);
            SysParams.OAConnection().RunSql(strSql);
        } 
        #endregion

        #region 绩效考核
        /// <summary>
        /// (岗位统计)统计某个岗位上的工作情况。可能是看某个人在几个时间段的工作情况，可能是看某个时间段内，所有相关人的工作情况。
        /// </summary>
        /// <param name="strWorkFlowName">工作流名称</param>
        /// <param name="strStepName">岗位名称</param>
        /// <param name="strTimeStart">开始时间</param>
        /// <param name="strTimeEnd">终止时间</param>
        /// <returns></returns>
        public ArrayList WorkStatusOfStep(String strWorkFlowName, String strStepName,
            String strTimeStart, String strTimeEnd)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }
            
            String sql = string.Format("select u.user_name,hist.userid,count(*) as CNT,sum(hist.exusedtime) as" +
                " TOTAL_TIME from st_work_item_hist hist left join st_instance ins on ins.iid = hist.iid" +
                " left join st_user u on u.userid = hist.userid where ins.wid in " +
                "(select wid from st_workflow wf where wf.wname='{0}') " +
                "and hist.step = '{1}' {2} group by hist.userid,u.user_name", strWorkFlowName,
                strStepName, strCondition);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                List<string> lstReturn = new List<string>();
                lstReturn.Add(drRecord["user_name"].ToString());
                lstReturn.Add(drRecord["userid"].ToString());
                lstReturn.Add(drRecord["CNT"].ToString());
                lstReturn.Add(drRecord["TOTAL_TIME"].ToString());

                string strStoreSql = string.Format("select distinct ins.iid,ins.name,ins.wid,ins.status,ins.priority,ins.accepted_time,ins.exbeginmonitor,ins.exusedtime from st_work_item_hist hist " +
                    "left join st_instance ins on ins.iid = hist.iid " +
                    "left join st_user u on u.userid = hist.userid " +
                    "where ins.wid in (select wid from st_workflow wf where wf.wname='{0}') " +
                            "and hist.step = '{1}' and u.userid='{2}' {3}"
                            , strWorkFlowName, strStepName, drRecord["userid"], strCondition);
                lstReturn.Add(strStoreSql);

                alSource.Add(lstReturn);
            }

            return alSource;
        }
        #endregion

        #region (部门统计)获取某个部门的所有人，在某个流程的所有岗位上的执行情况。时间的范围为案例的接收时间。
        /// <summary>
        /// (部门统计)获取某个部门的所有人，在某个流程的所有岗位上的执行情况。时间的范围为案例的接收时间。
        /// </summary>
        /// <param name="strWorkFlowName">流程名</param>
        /// <param name="strDepartmentId">部门id</param>
        /// <param name="strTimeStart">接件的开始时间</param>
        /// <param name="strTimeEnd">接件的终止时间</param>
        /// <param name="strOrderBy">排序的方式</param>
        /// <returns></returns>
        public ArrayList WorkStatusOfStepAndDepartment(String strWorkFlowName, String strDepartmentId,
            String strTimeStart, String strTimeEnd, string strOrderBy)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            
            String sql = string.Format("select u.user_name,hist.userid,hist.step,count(*) as CNT," +
                "sum(hist.exusedtime) as TOTAL_TIME from st_work_item_hist hist left join " +
                "st_instance ins on ins.iid = hist.iid " +
                " left join st_user u on u.userid = hist.userid where ins.wid in " +
                "(select wid from st_workflow wf where wf.wname='{0}') " +
                "and  hist.userid in (select userid from st_user_department where order_id= '{1}') {2} " +
                "group by hist.userid,u.user_name,hist.step order by {3}", strWorkFlowName,
                strDepartmentId, strCondition, strOrderBy);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                Dictionary<string, string> dicReturn = new Dictionary<string, string>();

                dicReturn.Add("USER_NAME", drRecord["user_name"].ToString());
                dicReturn.Add("USERID", drRecord["userid"].ToString());
                dicReturn.Add("STEP", drRecord["Step"].ToString());
                dicReturn.Add("CNT", drRecord["CNT"].ToString());
                dicReturn.Add("TOTAL_TIME", drRecord["TOTAL_TIME"].ToString());

                
                string strStoreSql = string.Format("select ins.* from st_work_item_hist hist " +
                    "left join st_instance ins on ins.iid = hist.iid " +
                    "left join st_user u on u.userid = hist.userid " +
                    "where ins.wid in (select wid from st_workflow wf where wf.wname='{0}') " +
                            "and hist.step = '{1}' and u.userid='{2}' {3}"
                            , strWorkFlowName, drRecord["step"], drRecord["userid"], strCondition);
                dicReturn.Add("detailSql", strStoreSql);

                alSource.Add(dicReturn);
            }

            return alSource;
        } 
        #endregion

        #region 个人统计
        /// <summary>
        /// (个人统计)
        /// </summary>
        /// <param name="strWorkFlowName">流程名</param>
        /// <param name="strStepName">岗位名称</param>
        /// <param name="strUserId">用户id</param>
        /// <param name="strTimeStart">接件起始时间</param>
        /// <param name="strTimeEnd">接件结束时间</param>
        /// <param name="strIntervalType">获取接收日期的类型（年、月、日）</param>
        /// <returns></returns>
        public ArrayList WorkStatusOfPersonalStep(String strWorkFlowName, String strStepName,
            String strUserId, String strTimeStart, String strTimeEnd, string strIntervalType)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            String intervalCondition = string.Empty;
            String intervalGroupByCondition = string.Empty;

            if (!string.IsNullOrEmpty(strIntervalType))
            {
                if (strIntervalType.Equals("day"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,extract(month from hist.accepted_time) month, extract(day from hist.accepted_time) as day ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time),extract(month from hist.accepted_time),extract(day from hist.accepted_time) ";
                }
                else if (strIntervalType.Equals("month"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,extract(month from hist.accepted_time) month ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time),extract(month from hist.accepted_time) ";
                }
                else if (strIntervalType.Equals("year"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time) ";
                }

            }

            
            String sql = string.Format("select {0},count(*) as CNT,sum(hist.exusedtime) as TOTAL_TIME " +
                " from st_work_item_hist hist left join st_instance ins on ins.iid = hist.iid " +
                " where ins.wid in (select wid from st_workflow wf where wf.wname='{1}') " +
                    " and  hist.step='{2}' and hist.userid='{3}' {4}  group by {5}",
                    intervalCondition, strWorkFlowName, strStepName, strUserId, strCondition,
                    intervalGroupByCondition);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                Dictionary<string, string> dicReturn = new Dictionary<string, string>();

                String year = drRecord["day"].ToString();
                String month = drRecord["month"].ToString();
                String day = drRecord["year"].ToString();

                dicReturn.Add("day", drRecord["day"].ToString());
                dicReturn.Add("month", drRecord["month"].ToString());
                dicReturn.Add("year", drRecord["year"].ToString());
                dicReturn.Add("CNT", drRecord["CNT"].ToString());
                dicReturn.Add("TOTAL_TIME", drRecord["TOTAL_TIME"].ToString());

                String strStoreSql = "select hist.iid from st_work_item_hist hist " +
                     "left join st_instance ins on hist.iid=ins.iid " +
                     " where 1=1  ";
                String strTimeCondition = string.Empty;//时间范围
                if (strIntervalType.Equals("day"))
                {
                    strTimeCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                    strTimeCondition += " and extract(month from hist.accepted_time)='" + month + "'";
                    strTimeCondition += " and extract(day from hist.accepted_time)='" + day + "'";
                }
                else if (strIntervalType.Equals("month"))
                {
                    strTimeCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                    strTimeCondition += " and extract(month from hist.accepted_time)='" + month + "'";
                }
                else if (strIntervalType.Equals("year"))
                {
                    strTimeCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                }
                strStoreSql += strTimeCondition;
                strStoreSql += " and ins.wid in " +
                " (select wid from st_workflow wf where wf.wname='" + strWorkFlowName + "') " +
                " and hist.step='" + strStepName + "' and hist.userid='" + strUserId + "'";
                strStoreSql += " group by hist.iid";
                strStoreSql = "select * from st_instance where iid in (" + strStoreSql + ")  ";

                dicReturn.Add("detailSql", strStoreSql);

                alSource.Add(dicReturn);
            }

            return alSource;
        } 
        #endregion

        #region 统计指定流程的所有案件情况
        /// <summary>
        /// 对案件进行统计。某个流程或全部流程，指定时间内或全部时间内，案件处理情况在各部门之间的分布
        /// </summary>
        /// <param name="strWorkFlowName">工作流名称</param>
        /// <param name="strTimeStart">起始时间</param>
        /// <param name="strTimeEnd">终止时间</param>
        /// <returns></returns>
        public Dictionary<string, string> StaticOfInstance(String strWorkFlowName,
            String strTimeStart, String strTimeEnd)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and ins.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and ins.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            if (!string.IsNullOrEmpty(strWorkFlowName))
            {
                strCondition += string.Format(" and wf.wname='{0}'", strWorkFlowName);
            }

            DataTable dtOut;

            //设置sql
            string status0 = "0";
            string status1 = "0";
            string status2 = "0";
            string exusedtimeTotal = "0";
            string instanceCount = "0";
            string alarm = "0";
            string overtime = "0";
            string priority0 = "0";
            string priority1 = "0";
            string priority2 = "0";

            Dictionary<string, string> lstReturn = new Dictionary<string, string>();

            //获取状态数据
            string strSqlStatus = string.Format(@"select status,count(iid) nCount from 
                st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}  group by status", strCondition);
            SysParams.OAConnection().RunSql(strSqlStatus, out dtOut);
            foreach (DataRow drRecord in dtOut.Rows)
            {
                if (drRecord["STATUS"].ToString() == "0")
                {
                    status0 = drRecord["nCount"].ToString();
                }
                else if (drRecord["STATUS"].ToString() == "1")
                {
                    status1 = drRecord["nCount"].ToString();
                }
                else if (drRecord["STATUS"].ToString() == "2")
                {
                    status2 = drRecord["nCount"].ToString();
                }
            }

            //获取优先级数据
            string strSqlPriority = string.Format(@"select priority,count(iid) nCount from 
                st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}  group by priority", strCondition);
            SysParams.OAConnection().RunSql(strSqlPriority, out dtOut);
            foreach (DataRow drRecord in dtOut.Rows)
            {
                if (drRecord["priority"].ToString() == "0")
                {
                    priority0 = drRecord["nCount"].ToString();
                }
                else if (drRecord["priority"].ToString() == "1")
                {
                    priority1 = drRecord["nCount"].ToString();
                }
                else if (drRecord["priority"].ToString() == "2")
                {
                    priority2 = drRecord["nCount"].ToString();
                }
            }

            //获取总时间
            string strTimeSql = string.Format(@"select sum(ins.exusedtime) total
                 from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}  ", strCondition);
            exusedtimeTotal = SysParams.OAConnection().GetValue(strTimeSql);

            //获取超期的案件数
            string strOvertimeSql = string.Format(@"select count(iid) nCount
                 from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid and ins.exusedtime>ins.extotaltime {0}", strCondition);
            overtime = SysParams.OAConnection().GetValue(strOvertimeSql);

            //获取报警的案件数
            string strAlermSql = string.Format(@"select count(iid) nCount
                 from st_instance ins,st_workflow wf where wf.wid = ins.wid 
                and ins.exusedtime>ins.exalarmtime and ins.exusedtime<=ins.extotaltime {0}", strCondition);
            alarm = SysParams.OAConnection().GetValue(strAlermSql);

            //获取统计基数sql
            String sql = string.Format(@"select count(ins.iid)  from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}  ", strCondition);
            instanceCount = SysParams.OAConnection().GetValue(sql);

            //统计记录
            lstReturn.Add("status0", status0);
            lstReturn.Add("status1", status1);
            lstReturn.Add("status2", status2);
            lstReturn.Add("exusedtimeTotal", exusedtimeTotal);
            lstReturn.Add("instanceCount", instanceCount);

            lstReturn.Add("alarm", alarm);
            lstReturn.Add("overtime", overtime);
            lstReturn.Add("priority0", priority0);
            lstReturn.Add("priority1", priority1);
            lstReturn.Add("priority2", priority2);
            lstReturn.Add("WNAME", strWorkFlowName);

            sql = string.Format(@"select ins.*  from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0} ", strCondition);
            lstReturn.Add("detailSql", sql);

            return lstReturn;
        } 
        #endregion

        #region 对案件进行统计。某个流程或全部流程，指定时间内或全部时间内，案件处理情况在各部门之间的分布
        /// <summary>
        /// 对案件进行统计。某个流程或全部流程，指定时间内或全部时间内，案件处理情况在各部门之间的分布
        /// </summary>
        /// <param name="strWorkFlowName">工作流名称</param>
        /// <param name="strOrderId">管理的组id</param>
        /// <param name="strTimeStart">开始时间</param>
        /// <param name="strTimeEnd">终止日期</param>
        /// <returns></returns>
        public Dictionary<string, string> StaticOfInstance(String strWorkFlowName, String strOrderId,
            String strTimeStart, String strTimeEnd)
        {
            //需要同时考虑刚开始办的，即hist表里没有的，也要考虑已经办结的，即item表里没有的
            //现在使用的是like，所以统计包括所选部门，以及下属部门，这不是说将下属部门的案件计入上属部门
            //如果一个案件经手多个部门的话，上面的条件不能过滤掉其他部门，或许sql不应该现在这样设计
            //ins.iid,ins.status一一对应，iid是ins的主键所以有了ins.iid后案例表中的所有字段都可以放到group by里
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strWorkFlowName))
            {
                strCondition += string.Format(" and wf.wname='{0}'", strWorkFlowName);
            }

            if (!string.IsNullOrEmpty(strOrderId))
            {
                string strConditionStart=string.Empty;
                if (!string.IsNullOrEmpty(strTimeStart))
                {
                    strConditionStart = string.Format(@" and a.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS'))",
                        strTimeStart);
                }

                string strConditionEnd = string.Empty;
                if (!string.IsNullOrEmpty(strTimeEnd))
                {
                    strConditionEnd = string.Format(@" and a.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS'))",
                         strTimeEnd);
                }

                strCondition += string.Format(@" and ins.iid in
                   (select distinct iid
                      from st_work_item_hist a
                     where a.userid in
                           (select userid from st_user_department where order_id = '{0}') {1} {2}
                    union
                    select distinct iid
                      from st_work_item b
                     where b.userid in
                           (select userid from st_user_department where order_id = '{0}'))",
                     strOrderId, strConditionStart, strConditionEnd);
            }
            else
            {
                if (!string.IsNullOrEmpty(strTimeStart))
                {
                    strCondition += string.Format(@" and ins.iid in
                   (select distinct iid
                      from st_work_item_hist a where a.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS'))",
                        strTimeStart);
                }

                if (!string.IsNullOrEmpty(strTimeEnd))
                {
                    strCondition += string.Format(@" and ins.iid in
                   (select distinct iid
                      from st_work_item_hist a where a.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS'))",
                        strTimeEnd);
                }
            }

            DataTable dtOut;

            //设置sql
            string status0 = "0";
            string status1 = "0";
            string status2 = "0";
            string exusedtimeTotal = "0";
            string instanceCount = "0";
            string alarm = "0";
            string overtime = "0";
            string priority0 = "0";
            string priority1 = "0";
            string priority2 = "0";

            Dictionary<string, string> lstReturn = new Dictionary<string, string>();

            //获取状态数据
            string strSqlStatus = string.Format(@"select status,count(iid) nCount from 
                st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}   group by status", strCondition);
            SysParams.OAConnection().RunSql(strSqlStatus, out dtOut);
            foreach (DataRow drRecord in dtOut.Rows)
            {
                if (drRecord["STATUS"].ToString() == "0")
                {
                    status0 = drRecord["nCount"].ToString();
                }
                else if (drRecord["STATUS"].ToString() == "1")
                {
                    status1 = drRecord["nCount"].ToString();
                }
                else if (drRecord["STATUS"].ToString() == "2")
                {
                    status2 = drRecord["nCount"].ToString();
                }
            }

            //获取优先级数据
            string strSqlPriority = string.Format(@"select priority,count(iid) nCount from 
                st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}   group by priority", strCondition);
            SysParams.OAConnection().RunSql(strSqlPriority, out dtOut);
            foreach (DataRow drRecord in dtOut.Rows)
            {
                if (drRecord["priority"].ToString() == "0")
                {
                    priority0 = drRecord["nCount"].ToString();
                }
                else if (drRecord["priority"].ToString() == "1")
                {
                    priority1 = drRecord["nCount"].ToString();
                }
                else if (drRecord["priority"].ToString() == "2")
                {
                    priority2 = drRecord["nCount"].ToString();
                }
            }

            //获取总时间
            string strTimeSql = string.Format(@"select sum(ins.exusedtime) total
                 from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}  ", strCondition);
            exusedtimeTotal = SysParams.OAConnection().GetValue(strTimeSql);

            //获取超期的案件数
            string strOvertimeSql = string.Format(@"select count(iid) nCount
                 from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid   and ins.exusedtime>ins.extotaltime {0}", strCondition);
            overtime = SysParams.OAConnection().GetValue(strOvertimeSql);

            //获取报警的案件数
            string strAlermSql = string.Format(@"select count(iid) nCount
                 from st_instance ins,st_workflow wf where wf.wid = ins.wid   
                and ins.exusedtime>ins.exalarmtime and ins.exusedtime<=ins.extotaltime {0}", strCondition);
            alarm = SysParams.OAConnection().GetValue(strAlermSql);

            //获取统计基数sql
            String sql = string.Format(@"select count(ins.iid)  from st_instance ins,st_workflow wf 
                where wf.wid = ins.wid {0}  ", strCondition);
            instanceCount = SysParams.OAConnection().GetValue(sql);

            //统计记录
            lstReturn.Add("status0", status0);
            lstReturn.Add("status1", status1);
            lstReturn.Add("status2", status2);
            lstReturn.Add("exusedtimeTotal", exusedtimeTotal);
            lstReturn.Add("instanceCount", instanceCount);

            lstReturn.Add("alarm", alarm);
            lstReturn.Add("overtime", overtime);
            lstReturn.Add("priority0", priority0);
            lstReturn.Add("priority1", priority1);
            lstReturn.Add("priority2", priority2);

            sql = string.Format(@"select ins.*
              from st_instance ins,st_workflow wf
             where ins.wid=wf.wid {0}  ", strCondition);
            lstReturn.Add("detailSql", sql);

            return lstReturn;
        } 
        #endregion

        #region 按人员进行绩效考核统计
        /// <summary>
        /// 按人员进行绩效考核统计
        /// </summary>
        /// <param name="wname"></param>
        /// <param name="stepName"></param>
        /// <param name="userID"></param>
        /// <param name="timeFrom"></param>
        /// <param name="timeTo"></param>
        /// <param name="intervalType"></param>
        /// <returns></returns>
        public DataTable statisticStepOfStaff(String wname, String stepName, String userID,
            String timeFrom, String timeTo, String intervalType)
        {

            String timeCondition = "";
            if (!string.IsNullOrEmpty(timeFrom))
            {
                timeCondition += " and hist.accepted_time>=" + DataFormat.S2Date(timeFrom);
            }

            if (!string.IsNullOrEmpty(timeTo))
            {
                timeCondition += " and hist.accepted_time<=" + DataFormat.S2Date(timeTo);
            }
            String intervalCondition = "";
            String intervalGroupByCondition = "";

            if (!string.IsNullOrEmpty(intervalType))
            {
                if (intervalType.Equals("day"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,extract(month from hist.accepted_time) month, extract(day from hist.accepted_time) as day ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time),extract(month from hist.accepted_time),extract(day from hist.accepted_time) ";
                }
                else if (intervalType.Equals("month"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,extract(month from hist.accepted_time) month,null day ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time),extract(month from hist.accepted_time) ";
                }
                else if (intervalType.Equals("year"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,null month,null day ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time) ";
                }

            }

            String sql = "select" + intervalCondition + ",count(*) as CNT,sum(hist.exusedtime) as TOTAL_TIME,null detailSql from st_work_item_hist hist" +
                    " left join st_instance ins on ins.iid = hist.iid" +
                    " where ins.wid in " +
                    "(select wid from st_workflow wf where wf.wname='" + wname + "') " +
                    "   and hist.step='" + stepName + "' and hist.userid='" + userID + "'" + timeCondition +
                    " group by " + intervalGroupByCondition;
            DataTable list;
            SysParams.OAConnection().RunSql(sql, out list);
            for (int i = 0; i < list.Rows.Count; i++)
            {
                DataRow m = list.Rows[i];
                String year = m["YEAR"].ToString();
                String month = m["MONTH"].ToString();
                String day = m["DAY"].ToString();

                String detailSql = "select hist.iid from st_work_item_hist hist " +
                        "left join st_instance ins on hist.iid=ins.iid " +
                        " where 1=1  ";
                String tCondition = "";//时间范围
                if (intervalType.Equals("day"))
                {
                    tCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                    tCondition += " and extract(month from hist.accepted_time)='" + month + "'";
                    tCondition += " and extract(day from hist.accepted_time)='" + day + "'";
                }
                else if (intervalType.Equals("month"))
                {
                    tCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                    tCondition += " and extract(month from hist.accepted_time)='" + month + "'";
                }
                else if (intervalType.Equals("year"))
                {
                    tCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                }
                detailSql += tCondition;
                detailSql += " and ins.wid in " +
                " (select wid from st_workflow wf where wf.wname='" + wname + "') " +
                " and hist.step='" + stepName + "' and hist.userid='" + userID + "'";
                detailSql += " group by hist.iid";
                detailSql = "select * from st_instance where iid in (" + detailSql + ")  and isdelete <>1";
                m["detailSql"] = detailSql;
            }
            return list;

        } 
        #endregion

        /// <summary>
        /// 获取不同工作流
        /// </summary>
        /// <returns></returns>
        public DataTable GetDisnictWorkFlow()
        {
            string sql = "Select Distinct wname From st_workflow ";
            DataTable dt;
            SysParams.OAConnection().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 查看工作流详情
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDetailFlow(string iid)
        {
            string sql = "SELECT wiid,userid from st_work_item_hist where iid=" + iid + " order by wiid ";
            DataTable dt;
            SysParams.OAConnection().RunSql(sql, out dt);
            if (dt.Rows.Count == 0)
            {
                sql = "SELECT wiid,userid from st_work_item where iid=" + iid + " order by wiid ";
                SysParams.OAConnection().RunSql(sql, out dt);
            }
            return dt;
        }


        /// <summary>
        /// 流程业务精确查询
        /// </summary>
        /// <param name="strFlowName">流程类型(名称)</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100623-->
        public DataTable GetExactQueryList(string strFlowName)
        {
            string strSql = string.Empty;
            DataTable dtTemp = new DataTable();
            strSql = string.Format(@"select *
                                      from (select i.iid,
                                                   w.wname 业务类型,
                                                   i.name 申请单位,
                                                   (select 联系人 from xt_instance_ext where iid = i.iid) 联系人,
                                                   (select 联系人电话 from xt_instance_ext where iid = i.iid) 联系人电话,
                                                   to_char(i.accepted_time, 'yyyy-mm-dd hh24:mi:ss') 接件时间,
                                                   i.priority 优先级,
                                                   i.status
                                              from st_instance i, st_workflow w
                                             where w.wid = i.wid
                                               and w.wname in ('{0}')
                                             order by i.iid)
                                     where 1 = 1",strFlowName);
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 为了速度，只列前1000条
        /// </summary>
        /// <param name="strYwlx">业务类型</param>
        /// <param name="strYxj">优先级</param>
        /// <param name="strSqdw">申请单位</param>
        /// <param name="strIid"></param>
        /// <returns></returns>
        public DataTable GetWorkFlow(string strYwlx, string strYxj, string strSqdw, string strIid)
        {
            DataTable dtReturn = new DataTable();
            string strSql = string.Format(@"select i.iid, w.wname 业务类型,i.name 申请单位,
                (select 联系人 from xt_instance_ext where iid=i.iid) 联系人,
                   to_char(i.EXBEGINMONITOR,'yyyy-mm-dd hh24:mi:ss') 接件时间,
                   i.priority 优先级,i.status from st_instance i,st_workflow w where 
                w.wid=i.wid and rownum<1000 and i.isdelete <>1");
            if (!string.IsNullOrEmpty(strYwlx))
            {
                strSql += string.Format(" and w.wname='{0}'", strYwlx);
            }
            if (!string.IsNullOrEmpty(strYxj))
            {
                strSql += string.Format(" and i.status={0}", strYxj);
            }
            if (!string.IsNullOrEmpty(strSqdw))
            {
                strSql += string.Format(" and i.name like '%{0}%'", strSqdw);
            }
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and i.iid='{0}'", strIid);
            }

            strSql += string.Format("  order by i.accepted_time ");

            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }


        #region 获得用户有权限的业务列表
        /// <summary>
        /// 获得用户有权限的业务列表
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetFlowNameByUser(string userId)
        {
            string strSql = "select distinct wname from st_workflow where wid in(select wid from st_instance where isdelete <>1)";

            if (!string.IsNullOrEmpty(userId))
            {
                bool IsSystemUser = StUserGroupHandle.IsSystemUser(userId);
                if (!IsSystemUser)//不是管理员及数据维护组，只能查自己的业务,除非设置过授权            
                {
                    strSql += string.Format(@" And (wid  In(Select wid From st_group_in_step a,st_group b Where a.gid=b.groupid And  
                    gid In(Select gid From st_user_group Where userid='{0}') )
                     OR wname in(SELECT TASK FROM XT_QUERY_RIGHT WHERE USERID='{0}'))", userId);
                }
            }
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }
        #endregion

        /// <summary>
        /// 删除流程
        /// </summary>
        /// <param name="iids"></param>
        /// <returns></returns>
        public string DeleteInstance(string iids)
        {
            System.String strSql = "";
            strSql = "select table_name from user_tables where table_name like 'UT_%'";
            DataTable rs;
            SysParams.OAConnection().RunSql(strSql, out rs);

            IDataAccess idsOperation = SysParams.OAConnection(true);
            string strReturn = string.Empty;
            try
            {
                for (int i = 0; i < rs.Rows.Count; i++)
                {
                    strSql = string.Format("delete from {0} where iid in {1}",
                        rs.Rows[i]["table_name"], iids);
                    idsOperation.RunSql(strSql);
                }
                strSql = "select res_value from st_dynamic_resource where type=2 and iid in " + iids;
                idsOperation.RunSql(strSql, out rs);
                for (int i = 0; i < rs.Rows.Count; i++)
                {
                    strSql = "delete from st_attachment where aid='" + System.Convert.ToString(rs.Rows[i]["res_value"]) + "'";
                    idsOperation.RunSql(strSql);
                }
                strSql = "delete from st_dynamic_resource where iid in " + iids;
                idsOperation.RunSql(strSql);
                strSql = "delete from st_work_item where iid in " + iids;
                idsOperation.RunSql(strSql);
                strSql = "delete from st_suspend where iid in " + iids;
                idsOperation.RunSql(strSql);
                strSql = "delete from st_work_item_hist where iid in " + iids;
                idsOperation.RunSql(strSql);
                strSql = "delete from st_instance where iid in " + iids;
                idsOperation.RunSql(strSql);
                strSql = "delete from xt_instance_ext where iid in " + iids;
                idsOperation.RunSql(strSql);

                idsOperation.Close(true);
            }
            catch (Exception ex)
            {
                idsOperation.Close(false);
                strReturn = "删除失败，原因是" + ex.Message;
            }
            return strReturn;
        }

        /// <summary>
        /// 获取岗位组
        /// </summary>
        /// <param name="strWorkItemId"></param>
        /// <returns></returns>
        public string GetStepGroup(string strWorkItemId)
        {
            string strSql = string.Format(@" select stepgroup from st_work_item where wiid={0}", strWorkItemId);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 判断是否终止过
        /// </summary>
        /// <param name="strStepName"></param>
        /// <returns></returns>
        public string GetISSTOP(string strStepName)
        {
            string strSql = string.Format(@"select IS_STOP from ST_GROUP where GROUP_NAME='{0}'", strStepName);
            return SysParams.OAConnection().GetValue(strSql);
        }
       
        /// <summary>
        /// 判断是否回退过
        /// </summary>
        /// <param name="strInstanceId"></param>
        /// <param name="strStepName"></param>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public string GetIsBack(string strInstanceId, string strStepName, string UserId)
        {
            string strSql = string.Format(@"Select max(wiid) From st_work_item_hist Where wiid In(
                                select prev_wiid from st_work_item_stack t 
                                Where IID={0} And step='{1}' AND UserId='{2}' ) ", strInstanceId, strStepName, UserId);
            return SysParams.OAConnection().GetValue(strSql);
        }
        /// <summary>
        /// 根据iid获取wid
        /// </summary>
        /// <param name="strIid"></param>
        /// <returns></returns>
        public string GetWnameByIid(string strIid)
        {
            string strSql = string.Format(@"select n.wname from st_instance m,st_workflow n 
                where m.wid=n.wid and m.iid='{0}' and m.isdelete <>1", strIid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        #region 办件查询业务查询列表
        
        /// <summary>
        /// 获取所有流程的状态信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetWorkflowStatus()
        {
            string strSql = @"select  w.wname,decode(i.status,2,'已办结',-2,'已终止',1,'正在办理') blzt,count(iid) bjs,sum(i.exusedtime)/count(iid) pjblsj
  from st_instance i, st_workflow w
 where w.wid = i.wid and rot=0 group by w.wname,i.status";
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 检索流程实例
        /// </summary>
        /// <param name="fromDate"></param>
        /// <param name="endDate"></param>
        /// <param name="ywlx"></param>
        /// <param name="yxj"></param>
        /// <param name="sqdw"></param>
        /// <param name="ywbh"></param>
        /// <param name="isComplete"></param>
        /// <param name="notComplete"></param>
        /// <param name="isCommand"></param>
        /// <param name="notCommand"></param>
        /// <param name="isStop"></param>
        /// <param name="notStop"></param>
        /// <param name="UserId"></param>
        /// <param name="searchWord"></param>
        /// <returns></returns>
        public DataTable SearchFlowDataGrid(string fromDate, string endDate, string ywlx, string yxj, string sqdw, string ywbh, string isComplete, string notComplete, string isCommand, string notCommand, string isStop, string notStop, string UserId, ref string searchWord)
        {

            string sqlStr = "";
            //是否办结
            if (isComplete == "on" && notComplete == "on")
            {
            }
            else if (!string.IsNullOrEmpty(notComplete))
            {
                sqlStr += " and i.status=1";
            }
            else if (!string.IsNullOrEmpty(isComplete))
            {
                sqlStr += " and i.status=2";
            }

            if (isStop == "on" && notStop == "on")
            {
            }
            else if (!string.IsNullOrEmpty(isStop))
            {
                sqlStr += " and i.status=-2";
            }
            else if (!string.IsNullOrEmpty(isComplete))
            {
                sqlStr += " and i.status<>-2";
            }

            //--经手条件
            if (isCommand == "on" && notCommand == "on")
            {
            }
            else if (isCommand == "on" && !string.IsNullOrEmpty(UserId))//已经手
            {
                sqlStr += (" and i.iid in (select 编号 from sv_work_item_list where userid='" + UserId + "')");
            }
            else if (notCommand == "on" && !string.IsNullOrEmpty(UserId))//未经手
            {
                sqlStr += (" and  not exists (select 编号 from sv_work_item_list where 编号=i.iid and userid='" + UserId + "')");
            }
            else
            {
                sqlStr += " and 1!=1";
            }

            if (!string.IsNullOrEmpty(fromDate))
            {
                sqlStr += " and   i.exbeginmonitor > to_date('" + fromDate + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                sqlStr += " and   i.exbeginmonitor < to_date('" + endDate + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
            }
            //业务类型
            if (!string.IsNullOrEmpty(ywlx))
            {
                sqlStr += " and w.wname ='" + ywlx + "'";
            }
            //优先级
            if (!string.IsNullOrEmpty(yxj))
            {
                sqlStr += " and i.priority=" + yxj + "";
            }
            //申请单位
            if (!string.IsNullOrEmpty(sqdw))
                sqlStr += " and i.name like '%" + sqdw + "%'";
            //编号
            if (!string.IsNullOrEmpty(ywbh))
            {
                sqlStr += " and i.iid like '%" + ywbh + "%'";
            }

            string SqlInstance = string.Format(@"select i.iid, w.wname 业务类型,i.name 申请单位,(select 联系人 from
                xt_instance_ext where iid=i.iid) 联系人,
                i.ACCEPTED_TIME 接件时间,i.priority 优先级,i.status from st_instance i,st_workflow w   
                where w.wid=i.wid and i.isdelete <>1 {0} order by i.iid  DESC", sqlStr);
            searchWord = sqlStr;
            DataTable dtOut;
            SysParams.OAConnection().RunSql(SqlInstance, out dtOut);
            return dtOut;
        }
        #endregion

        #region 展示节点步骤
        /// <summary>
        /// 展示节点步骤
        /// </summary>
        /// <param name="myArray">myArrayNum</param>
        /// <returns></returns>
        public string ShowStep(string myArray)
        {
            string SqlInstance = string.Format(@"select t.step from st_work_item t where t.active<>-1 and t.iid = '{0}'", myArray);
            return SysParams.OAConnection().GetValue(SqlInstance);
        }
        #endregion

        #region 办件查询业务查询图表
        /// <summary>
        /// 查询图表
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <param name="nFlag"></param>
        /// <returns></returns>
        public DataTable ShowCollectDataByChart(string sqlWhere,string nFlag)
        {
            string SqlInstance = "";
            switch(nFlag)
            {
                case "1"://按流程统计
                    SqlInstance = string.Format(@"select count(i.iid) count,w.wname wname from st_instance i,st_workflow w  where w.wid=i.wid {0}  group by w.wname", sqlWhere);
                    break;
                case "2"://按优先级统计
                    SqlInstance = string.Format(@"select count(i.iid) count,decode(i.priority,'0','一般','1','加急','2','特急') priority from st_instance i,st_workflow w   where w.wid=i.wid {0}  group by i.priority ", sqlWhere);
                    break;
                case "3"://按月统计接件数
                    SqlInstance = string.Format(@"select count(i.iid) count,to_char(i.accepted_time,'mm') themonth from st_instance i,st_workflow w where w.wid=i.wid {0}  group by to_char(i.accepted_time,'mm')", sqlWhere);
                    break;
                case "4"://按办理状态统计
                    SqlInstance = string.Format(@"select count(i.iid) count,decode(i.status,'1','正在办理','2','已办结','-2','已终止') status from st_instance i,st_workflow w   where w.wid=i.wid  {0}  group by i.status ", sqlWhere);
                    break;
            }
            DataTable dtOut;
            SysParams.OAConnection().RunSql(SqlInstance, out dtOut);
            return dtOut;
        }
        
        #endregion

        /// <summary>
        /// 获取流程附件信息
        /// </summary>
        /// <param name="strYwlx">业务类型</param>
        /// <param name="strYxj">优先级</param>
        /// <param name="strSqdw">申请单位</param>
        /// <param name="strIid">办件编号</param>
        /// <returns></returns>
        public static DataTable GetInstanceInfo(string strYwlx, string strYxj, string strSqdw, string strIid)
        {
            string strSql = "select i.iid 编号, w.wname 业务类型,i.name 申请单位,(select count(*) from st_dynamic_resource where iid=i.iid) 附件总数,to_char(i.EXBEGINMONITOR,'yyyy-mm-dd hh24:mi:ss') 接件时间,i.PRIORITY 优先级,(select 联系人 from xt_instance_ext where iid=i.iid) 联系人 from st_instance i,st_workflow w where w.wid=i.wid";

            if (!string.IsNullOrEmpty(strYwlx))
            {
                strSql += string.Format(" and w.wname='{0}'", strYwlx);
            }
            if (!string.IsNullOrEmpty(strYxj))
            {
                strSql += string.Format(" and i.PRIORITY={0}", strYxj);
            }
            if (!string.IsNullOrEmpty(strSqdw))
            {
                strSql += string.Format(" and i.name like '%{0}%'", strSqdw);
            }
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and  i.iid='{0}'", strIid);
            }
            //接收搜索串
            DataTable dtSource;
            SysParams.OAConnection().RunSql(strSql, out dtSource);
            return dtSource;
        }

        /// <summary>
        /// 从已办件中执行回退
        /// </summary>
        /// <param name="strIID">办件编号</param>
        /// <returns></returns>
        public void ReturnTaskFromComplete(string strIID)
        {
            OracleParameter param1 = new OracleParameter();
            param1.ParameterName = "diid";
            param1.DbType = System.Data.DbType.Decimal;
            param1.Value = strIID;

            IDataParameter[] paramArray = new OracleParameter[1];
            paramArray[0] = param1;
            SysParams.OAConnection().RunProc("Return_complete_task", ref paramArray);
        }

    }
}
